package db;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.xml.bind.JAXBException;
import org.xml.sax.SAXException;
import xml.Parser;
import xml.jxb.Project;

/**
 *
 * @author abnormal
 */
public class H2Adapter {

    private Connection con;
    private PreparedStatement ps;
    /**
     * 
     * @throws ClassNotFoundException
     * @throws SQLException 
     */
    public H2Adapter() throws ClassNotFoundException, SQLException {
        Class.forName("org.h2.Driver");
        try {
            
            con = DriverManager.getConnection("jdbc:h2:/data/main;IFEXISTS=TRUE", "admin", "password");
        } catch (Exception ex) {
            con = DriverManager.getConnection("jdbc:h2:/data/main", "admin", "password");
            ps = con.prepareStatement("CREATE TABLE projects (projid INTEGER NOT NULL auto_increment, userid INTEGER NOT NULL, project BLOB NOT NULL, PRIMARY KEY(projid))");
            ps.execute();
        }
    }
    /**
     * 
     * @param userId
     * @return
     * @throws SQLException
     * @throws JAXBException
     * @throws SAXException 
     */
    public ArrayList<Project> getProjectList(int userId) throws SQLException, JAXBException, SAXException {
        ps = getCon().prepareStatement("SELECT project from public.projects WHERE userid=?");
        ps.setInt(1, userId);
        ResultSet result = ps.executeQuery();
        ArrayList<Project> _result = new ArrayList<Project>();
        while(result.next()){
            Parser parser = new Parser(null);
            Blob blob = result.getBlob(1);
            parser.loadXML(blob.getBinaryStream());
            _result.add(parser.getProject());
        }
        getCon().close();
        return _result;
    }

    /***
     * 
     * @param userId
     * @param _project
     * @throws IOException
     * @throws SQLException
     * @throws JAXBException
     * @throws SAXException 
     */
    public void saveProject(int userId, Project _project) throws IOException, SQLException, JAXBException, SAXException {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        Parser parser = new Parser(null);
        parser.loadProject(_project);
        parser.StoreXML(baos);
        ps = getCon().prepareStatement("INSERT INTO public.projects (userid,project) VALUES(?,?)");
        ps.setInt(1, userId);
        ps.setBytes(2, baos.toByteArray());
        ps.execute();
        getCon().close();
    }

    @Override
    @SuppressWarnings("FinalizeDeclaration")
    protected void finalize() throws Throwable {
        super.finalize();
        getCon().close();
    }
    //test

    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, JAXBException, SAXException {
        H2Adapter e = new H2Adapter();
        e.saveProject(41, new Project());
        
    }

    /**
     * @return the con
     */
    public Connection getCon() {
        return con;
    }

    /**
     * @param con the con to set
     */
    public void setCon(Connection con) {
        this.con = con;
    }
}
